package com.item.mapper;

import com.item.pojo.*;
import org.apache.ibatis.annotations.*;
import org.springframework.security.access.method.P;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
@Mapper
public interface foodmapper {
    @Select("SELECT * FROM `menuitem` where type LIKE CONCAT('%', #{type}, '%') and isdelete=0")
    List<MenuItem>  getfood(@Param("type") String type);

    @Select("SELECT * FROM `order` WHERE user_id = #{userId} AND status = '正在下单'")
    Order findOrderByUserIdAndStatus(@Param("userId") int userId);

    // 插入订单信息
    @Insert("INSERT INTO `order` (user_id,ordertime, totalamount, `status`) VALUES (#{userId},now(), #{totalAmount}, '正在下单')")
    void insertOrder(@Param("userId") int userId, @Param("totalAmount") BigDecimal totalAmount);

    // 更新订单信息
    @Update("UPDATE `order` SET totalamount = totalamount + #{price} WHERE user_id = #{userId} AND status = '正在下单'")
    void updateOrder(@Param("userId") int userId, @Param("price") BigDecimal price);

    // 插入订单详情信息
    @Insert("INSERT INTO orderdetail (order_id, item_id,  quanntite, unitprice) VALUES (#{orderId}, #{itemId}, #{quantity}, #{unitPrice})")
    void insertOrderDetail(@Param("orderId") int orderId, @Param("itemId") int itemId, @Param("quantity") int quantity, @Param("unitPrice") BigDecimal unitPrice);

    @Select("SELECT DISTINCT item_id, quanntite FROM orderdetail WHERE order_id = #{orderId}")
    List<OrderDetail> findOrderDetailsByOrderId(int orderId);


    @Select("SELECT * FROM menuitem WHERE id = #{itemId}")
    MenuItem findMenuItemById(int itemId);

    @Select("SELECT SUM(quanntite )FROM orderdetail where item_id in(SELECT id FROM menuitem WHERE itemname= #{itemname}) and order_id in(SELECT id FROM `order` WHERE user_id = #{userId} AND status = '正在下单')")
          int findQuantiteByITEMNAME(String itemname,int userId);

    @Delete("DELETE FROM orderdetail WHERE order_id = #{orderId} AND item_id = #{itemId}")
    void deleteOrderDetail(@Param("orderId") int orderId, @Param("itemId") int itemId);

    @Update("UPDATE `order` SET status = '等待支付' , ordertime = now() WHERE user_id = #{userId} AND status = '正在下单'")
    void updateOrderstatus(@Param("userId") int userId);


    @Select("SELECT sum(quanntite*unitprice) FROM `orderdetail` WHERE order_id=#{orderId}")
    BigDecimal updateOrderamount(@Param("orderId")int orderId);

    // 更新订单信息
    @Update("UPDATE `order` SET totalamount =  #{price} WHERE user_id = #{userId} AND status = '正在下单'")
    void updateOrderprice(@Param("userId") int userId, @Param("price") BigDecimal price);


    @Select("SELECT sum(quanntite) FROM orderdetail WHERE item_id = #{itemId} AND order_id = #{orderId} ")
    int getTotalQuantityByItemId(@Param("itemId") int itemId ,@Param("orderId") int orderId);

    @Update("UPDATE menuitem SET Sales = Sales + #{quantity} WHERE id = #{itemId}")
    void updateSalesById(@Param("itemId") int itemId, @Param("quantity") int quantity);

    @Select("SELECT * FROM `menuitem` where itemname LIKE CONCAT('%', #{itemname}, '%')")
    List<MenuItem>  searchfood(@Param("itemname") String itemname);

    // 插入订单信息
    @Insert("INSERT INTO `order` (user_id,ordertime, totalamount, `status`) VALUES (#{userId},now(), #{totalAmount}, '等待支付')")
    void insertsepcialOrder(@Param("userId") int userId, @Param("totalAmount") BigDecimal totalAmount);

    @Select("SELECT* FROM `menuitem` where id=#{id}")
    MenuItem getmenu(@Param("id") int id);

    @Update("UPDATE menuitem SET quantity=quantity-1,version=version+1 WHERE id=#{id} AND version=#{version}")
    void Spikesmenu(@Param("id") int id,@Param("version") int version);

    @Select("SELECT * FROM `order` WHERE user_id = #{userId} AND status = '等待支付'")
    Order findSepcialOrderByUserIdAndStatus(@Param("userId") int userId);

    @Select("SELECT * FROM `orderdetail` WHERE item_id = #{itemId} AND order_id = #{orderId}")
    OrderDetail findOrderdeitalByItemIdAndOrderId(@Param("itemId") int itemId, @Param("orderId") int orderId);

    @Update("UPDATE orderdetail SET quanntite = quanntite + #{amount} WHERE order_id = #{orderId} AND item_id = #{itemId}")
    void updateOrderDetailQuantity(@Param("amount") int amount, @Param("orderId") int orderId, @Param("itemId") int itemId);

    @Select("SELECT * FROM `order` WHERE user_id = #{userId} and status='等待支付' ")
    Order findallOrderByUserIdAndStatus(@Param("userId") int userId);


    @Select("SELECT * FROM itempack where isdelete=0 ")
    List<itempack> getitempack();

    @Select("SELECT * FROM `itemcomment` WHERE item_id= #{itemId} and pid=0 and isdelete=0  ORDER BY weight DESC")
    List<ItemComment> getcomment(@Param("itemId") int itemId);

@Select("SELECT * FROM `itemcomment` WHERE pid= #{pid} AND item_id= #{itemId} and isdelete=0  ORDER BY weight DESC")
    List<ItemComment> getdowncomment(@Param("pid") int pid,@Param("itemId") int itemId);

@Select("select * from users where id=#{id}")
    User finduserdetailByid(@Param("id") int id);

    @Insert("INSERT INTO `itemcomment` (`comment`, `rating`, `pid`, `user_id`,`commenttime`, `item_id`) VALUES (#{comment},#{rating},#{pid},#{userId},now(),#{itemId});")
    Boolean insertcomment(@Param("comment") String comment, @Param("rating") double rating, @Param("pid") int pid, @Param("userId") int userId, @Param("itemId") int itemId);

    @Update("UPDATE `order` SET status= CONCAT('完成支付', #{number}) WHERE user_id=#{userId} AND status='等待支付'")
            Boolean finshapiplyorder(@Param("userId") int userId,@Param("number") String number);


    @Select("SELECT * FROM `orderdetail` WHERE order_id IN (SELECT id FROM `order` WHERE user_id = #{userId} AND status LIKE CONCAT('%', #{number}, '%'))")
    List<OrderDetail> findorderdetailByorderid(@Param("userId") int  userId,@Param("number") String number);

    @Select("SELECT SUM(quanntite )FROM orderdetail where item_id in(SELECT id FROM menuitem WHERE itemname= #{itemname}) and order_id in(SELECT id FROM `order` WHERE user_id = #{userId} AND status LIKE CONCAT('%', #{number}, '%'))")
    int findQuantiteByitnameandnumber(String itemname,int userId,String number);

    @Select("SELECT totalamount FROM `order` WHERE user_id = #{userId} AND status LIKE CONCAT('%', #{number}, '%')")
    BigDecimal findtotalamountByuseridandnumber(int userId,String number);

    @Select("SELECT id FROM `order` WHERE user_id = #{userId} AND status LIKE CONCAT('%', #{number}, '%')")
    int findorderidByuseridandnumber(int userId,String number);

    @Select("SELECT status FROM `order` WHERE user_id = #{userId} AND status LIKE CONCAT('%', #{number}, '%')")
    String findorderstatusByuseridandnumber(int userId,String number);

    @Select("SELECT * FROM `order` WHERE user_id = #{userId} ORDER BY id DESC")
    List<Order>findorderByuserid(int userId);

    @Select("SELECT * FROM menuitem WHERE id in (SELECT item_id FROM orderdetail WHERE order_id =#{orderId})")
    List<MenuItem> finditemlistByOrderId(int orderId);

    @Select("SELECT * FROM `orderdetail` WHERE order_id=#{orderId}")
    List<OrderDetail> findorderdetailByorderId(@Param("orderId") int  orderId);

    @Select("SELECT SUM(quanntite )FROM orderdetail where item_id in(SELECT id FROM menuitem WHERE itemname= #{itemname}) and order_id =#{orderId}")
    int findQuantiteByorderId(String itemname,int orderId);

    @Select("SELECT totalamount FROM `order` WHERE id=#{orderId}")
    BigDecimal findtotalamountByorderId(int orderId);

    @Select("SELECT id FROM `order` WHERE id = #{orderId}")
    int findorderidByorderId(int orderId);

    @Select("SELECT status FROM `order` WHERE id = #{orderId}")
    String findorderstatusByorderId(int orderId);
    @Select("SELECT * FROM `order` WHERE user_id = #{userId} AND status LIKE CONCAT('%', '完成支付', #{number}, '%')")
    Order findAplyOrderByUserIdAndStatus(@Param("userId") int userId, @Param("number") String number);

    @Select("SELECT* FROM coupons WHERE  end_date>=now() and start_date<=now() and id in(SELECT coupon_id FROM `user_coupons` WHERE user_id=#{userId} and status='未使用')")
    List<Coupon> getunusecouponByuserId(@Param("userId") int userId);
    @Select("select* from coupons where id =#{id} ")
    Coupon getcouponByid(@Param("id") int id);

    @Update( "UPDATE `user_coupons` SET status='已使用',redeem_date= now()  WHERE user_id=#{userId} and coupon_id=#{couponId} and status='未使用'")
    void changestatuscoupon(@Param("couponId") int couponId,@Param("userId") int userId);
    @Select("SELECT * FROM `coupons` where isdelete=0")
    List<Coupon>getallCoupons();
    @Select("SELECT * FROM `coupons` ")
    List<Coupon>getallCouponsByground();
     @Select("SELECT `status` FROM `user_coupons` where user_id=#{userId} and coupon_id=#{couponId}")
    String getcouponstatus( @Param("couponId") int couponId,@Param("userId") int userId);

   @Select("SELECT * FROM `user_coupons` WHERE user_id=#{userId} and coupon_id=#{couponId}")
UserCoupon getuserCouponByuserIdandcouponId(@Param("userId") int userId,@Param("couponId") int couponId);

   @Insert("insert into `user_coupons`(`user_id`,`coupon_id`,`redeem_date`,`status`) values(#{userId},#{couponId},now(),'未使用')")
    void insertuserCoupon(@Param("userId") int userId,@Param("couponId") int couponId);


   @Update("UPDATE `coupons` SET version=#{version}+1,number=#{number}-1 WHERE id=#{id} and version=#{version}")
   void deletcoupon(@Param("id") int id,@Param("version") int version,@Param("number") int number);

   @Select("SELECT * FROM `itemcomment`")
   List<ItemComment> getallcomment();

 /**
 * 根据用户ID列表获取用户推荐的菜单项列表。
 * 此方法通过查询与指定用户ID关联的itemcomment表中的item_id，
 * 并在menuitem表中查找对应菜单项。
 *
 * @param userId 一个整数列表，包含需要获取推荐菜单项的用户ID。
 * @return 返回一个菜单项列表，这些菜单项是根据用户ID列表中的用户推荐评论得到的。
 */
@Select({
    "<script>",
    "select * from `menuitem` ",
    "where id in (",
    "select item_id from `itemcomment` where user_id in",
    "<foreach item='id' collection='userId' open='(' separator=',' close=')'>",
    "#{id}",
    "</foreach>",
    ")",
    "and isdelete = 0",
    "</script>"
})
List<MenuItem> getitemByuserrecommenduserId(@Param("userId") List<Integer> userId);


 @Insert("INSERT INTO `order` (`user_id`, `totalamount`, `status`, `ordertime`) VALUES (#{userId}, #{totalamount},'等待支付', now());")
 Boolean insertitmepacktoOrder(@Param("totalamount") BigDecimal totalamount,@Param("userId") int userId);

 @Select("select * from `order` where user_id=#{userId} and status='等待支付'")
    Order findorderByuseridandstatus(int userId);

 @Select("select item_id from `packitem_item` where pack_id=#{packId}")
    List<Integer> getitemIdBypackId(int packId);

 @Select("select * from `menuitem` where id in (#{itemId})")
    MenuItem getitemByitemId(int itemId);
 @Insert("insert into `orderdetail`(`order_id`,`item_id`,`quanntite`,`unitprice`) values(#{orderId},#{itemId},#{quanntite},#{price})")
    void insertorderdetail(@Param("orderId") int orderId,@Param("itemId") int itemId,@Param("quanntite") int quanntite,@Param("price") BigDecimal price);
@Select("select * from menuitem where id in (select item_id from packitem_item  where pack_id=#{packId})")
    List<MenuItem> getListmenuBypackId(@Param("packId") int packId);

    @Select({
            "<script>",
            "select * from `menuitem` where id in ",
            "<foreach item='id' collection='itemId' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",

            "</script>"
    })
    List<MenuItem> getitemByuserrecommenditemId(@Param("itemId") List<Integer> itemId);

@Select("SELECT * FROM `menuitem` ORDER BY Sales DESC")
    List<MenuItem> getmenudesc();






    @Select("select * from `orderdetail` where order_Id=#{orderId}")
    List<OrderDetail> getorderdetailByorderId(int orderId);

    @Update("UPDATE `menuitem` SET Sales = Sales - #{quantity} WHERE `id` = #{id} ")
    Boolean decreseitemsalas(@Param("id") int id,@Param("quantity") int quantity);

@Delete("DELETE FROM `orderdetail` WHERE `order_id` = #{orderId}")
    void deletOrderDetailByorderId(int orderId);

@Select("SELECT * FROM `itemcomment` WHERE user_id=#{userId}")
List<ItemComment> itemcommentListByuserId(int userId);

@Update("Update `order` SET  status= CONCAT('已退款', #{number}) where `id`=#{id}")
    Boolean deletorderByuseridandstatus(@Param("id") int id,@Param("number") String number);

@Delete("DELETE FROM `orderdetail` WHERE `id` = #{orderId}")
Boolean deleteorderdetailByorderId(int orderId);

@Delete("DELETE FROM `order` WHERE `user_id` = #{userId} and status='正在下单'")
Boolean deleteorderByuserIdandStatus(@Param("userId") int userId);
//从这里开始都是food的管理mapper层

    @Select("SELECT * FROM `users`")
    List<User> getalluser();


@Update("UPDATE `users` SET password=#{password} WHERE username=#{username}")
    void updateuser(@Param("username") String username ,@Param("password") String password);

@Update("UPDATE `users` SET username=#{username},email=#{email},age=#{age},phone=#{phone},imageurl=#{imageurl} WHERE id=#{id}")
     void updateuserByusernameByemailByageByphoneByimageurl( @Param("id") int id,@Param("username") String username,@Param("email") String email,@Param("age") int age,@Param("phone") String phone,@Param("imageurl") String imageurl);

@Select("SELECT * FROM `role`")
List<Role> getallrole();

@Select("SELECT id FROM  `role` WHERE id in (SELECT role_id FROM `user_role` where user_id=#{userId})")
List<Integer> getallroleByuserid(@Param("userId") int userId);
@Insert("INSERT INTO `user_role` (`user_id`, `role_id`) VALUES (#{userId}, #{roleId})")
Boolean insertuserRoleByuseridandroleid(@Param("userId") int userId,@Param("roleId") int roleId);

@Select("SELECT banned FROM `users` WHERE id=#{userId}")
int finduserstatusByuserId(int userId);

@Update("UPDATE `users` SET banned=#{banned} WHERE id=#{userId}")
Boolean UpadteusertatusByuserId( @Param("userId") int userId,@Param("banned") int banned);


@Select("SELECT * FROM `menuitem`")
List<MenuItem> getmenuList();


@Insert("INSERT INTO `menuitem` (itemname, price, description, imageurl, quantity,type,isdelete) VALUES " +
        "(#{itemname}, #{price}, #{description}, #{imageurl}, #{quantity},#{type})")
void insertMenuItem(@Param("itemname") String itemname,
                   @Param("price") BigDecimal price,
                   @Param("description") String description,
                   @Param("imageurl") String imageUrl,
                   @Param("quantity") Integer quantity,
                   @Param("type") String type,
                    @Param("isdelete") int isdelete);


@Update("UPDATE `menuitem` SET itemname=#{itemname},price=#{price},description=#{description},imageurl=#{imageurl},quantity=#{quantity},type=#{type} WHERE id=#{id}")
Boolean updateitemByitem( @Param("id") int id, @Param("itemname") String itemname,
                          @Param("price") BigDecimal price,
                          @Param("description") String description,
                          @Param("imageurl") String imageUrl,
                          @Param("quantity") Integer quantity,
                          @Param("type") String type);

@Update("UPDATE `menuitem` SET specialPrice=#{specailPrice},type=#{type},quantity=#{quantity} WHERE id=#{itemId}")
Boolean updateitemspecialPriceandtypeandquantityByitemId( @Param("itemId") int itemId,@Param("specailPrice") BigDecimal specailPrice,@Param("type") String type,@Param("quantity") int quantity);



@Update("UPDATE `menuitem` SET isdelete=#{isdelete} WHERE id=#{id}")
Boolean updateitemstatusByitemid(@Param("id") int id,@Param("isdelete") int isdelete);

@Update("UPDATE `menuitem` SET price=#{price} WHERE id=#{id}")
Boolean updateitempriceByitemid(@Param("id") int id,@Param("price") BigDecimal price);


@Select("SELECT * FROM `order`")
List<Order> getorderBybackground();

@Update( "UPDATE `order` SET status=CONCAT(#{status}, #{number}) WHERE id=#{orderId}")
    Boolean updateordersatusByorderId( @Param("orderId") int orderId,@Param("status") String status,@Param("number") String number);



 @Update( "UPDATE `menuitem` SET price=price*#{discount}")
    Boolean Updatediccountitemprice( @Param("discount") BigDecimal discount);

 @Update( "UPDATE `menuitem` SET price=price*#{discount} WHERE type LIKE CONCAT('%', #{type}, '%')")
    Boolean UpdatediccountitempriceBytype(@Param("discount") BigDecimal discount,@Param("type") String type);

 @Update( "UPDATE `menuitem` SET price=price/#{discount}")
 Boolean recoveritemprcie( @Param("discount") BigDecimal discount);

 @Update( "UPDATE `menuitem` SET price=price/#{discount} WHERE type LIKE CONCAT('%', #{type}, '%')")
 Boolean recoveritemprcieBytype(@Param("discount") BigDecimal discount,@Param("type") String type);

 @Insert("INSERT INTO `coupons` (description, discount_value, minimum_spend, maximum_spend, start_date, end_date, number, version, member, isdelete) VALUES (#{description}, #{discountValue}, #{minimumSpend}, #{maximumSpend}, #{startDate}, #{endDate}, #{number}, #{version}, #{member}, #{isdelete})")
    void insertcoupon(@Param("description") String description,@Param("discountValue") BigDecimal discountValue, @Param("minimumSpend") int minimum_spend, @Param("maximumSpend") BigDecimal maxmum_spend,@Param("startDate") LocalDateTime start_date,@Param("endDate") LocalDateTime end_date,@Param("number") int number, @Param("version") int version, @Param("member") String member, @Param("isdelete") int isdelete);

 @Update("UPDATE `coupons` SET description=#{description},discount_value=#{discountValue},minimum_spend=#{minimumSpend},maximum_spend=#{maximumSpend},start_date=#{startDate},end_date=#{endDate},number=#{number},member=#{member},isdelete=#{isdelete} WHERE id=#{id}")
 Boolean updatecouponBYid( @Param("id") int id,@Param("description") String description,@Param("discountValue") BigDecimal discountValue, @Param("minimumSpend") int minimum_spend, @Param("maximumSpend") BigDecimal maxmum_spend,@Param("startDate") LocalDateTime start_date,@Param("endDate") LocalDateTime end_date,@Param("number") int number, @Param("member") String member, @Param("isdelete") int isdelete);


 @Insert("INSERT INTO `user_coupons` (`user_id`, `coupon_id`, `status`) VALUES (#{userId}, #{couponId},'未使用')")
 Boolean insertUsercouponByuseridandcouponidandsatus(@Param("userId") int userId,@Param("couponId") int couponId);

 @Update("UPDATE `user_coupons` SET status='未使用' WHERE user_id=#{userId} AND coupon_id=#{couponId}")
Boolean updateUsercouponSatusByuseridandcouponidandsatus(@Param("userId") int userId,@Param("couponId") int couponId);

 @Select("SELECT * FROM `users` WHERE id IN (SELECT user_id FROM `user_coupons` WHERE coupon_id=#{couponId} and status='未使用')")
 List<User> findUserBycouponId(int couponId);


 @Update("UPDATE `coupons` SET isdelete=#{isdelete} WHERE id=#{id}")
    Boolean deletcouponByid(int id,@Param("isdelete") int isdelete);

 @Select("SELECT * FROM `itemcomment`")
 List<ItemComment> getallitemcomment();

 @Update("UPDATE `itemcomment` SET isreply=#{isreply} WHERE id=#{id}")
 Boolean updatecommentreplyByid( @Param("id") int id, @Param("isreply")int isreply);

 @Insert("INSERT INTO `itemcomment` (comment, commenttime, rating, item_id, user_id, isdelete, pid, weight, isreply) VALUES (#{comment},now(), #{rating}, #{item_id}, #{user_id}, #{isdelete}, #{pid}, #{weight}, #{isreply})")
 Boolean insertcommentByidanditemid(@Param("comment") String comment, @Param("rating") double rating,  @Param("item_id") int itemId, @Param("user_id") int userId, @Param("isdelete") int isdelete, @Param("pid") int pid, @Param("weight") int weight, @Param("isreply") int isreply);


 @Update("UPDATE `itemcomment` SET weight=#{weight} WHERE id=#{id}")
 Boolean updatecommentweightByid( @Param("id") int id, @Param("weight") int weight);

 @Select("SELECT * FROM `itemcomment` WHERE id=#{id}")
 ItemComment getcommentByid(int id);

 @Update("UPDATE `itemcomment` SET isdelete=#{isdelete} WHERE id=#{id}")
 Boolean updatecommentisdeleteByid( @Param("id") int id, @Param("isdelete") int isdelete);

@Select("SELECT * FROM `itempack`")
 List<itempack> getallitempackBybackground();

@Insert("INSERT INTO `itempack` (description, allprice, image_url, isdelete) VALUES (#{description}, #{allprice}, #{imageUrl}, #{isdelete})")
Boolean insertitempackBybakground(@Param("description") String description,@Param("allprice") double allprice,@Param("imageUrl") String imageUrl,@Param("isdelete") int isdelete);


@Update("UPDATE `itempack` SET description=#{description},image_url=#{imageUrl} WHERE id=#{id}")
Boolean updateitempackdescriptionByid(@Param("id") int id,@Param("description") String description,@Param("imageUrl") String imageUrl);


@Delete("DELETE FROM `packitem_item` WHERE item_id=#{itemId} and pack_id=#{packId}")
Boolean deletepackitemItemByitemIdandpackId(@Param("itemId") int itemId,@Param("packId") int packId);

@Insert("INSERT INTO `packitem_item` (pack_id, item_id) VALUES (#{packId}, #{itemId})")
    Boolean insertpackitemItemByitemIdandpackId(@Param("itemId") int itemId,@Param("packId") int packId);

@Update("UPDATE `itempack` SET allprice=#{allprice} WHERE id=#{id}")
Boolean updateitempackallpriceByid( @Param("id") int id, @Param("allprice") BigDecimal allprice);

@Select("SELECT * FROM `itempack` WHERE id=#{id}")
itempack getitempackByid(int id);
@Update("UPDATE `itempack` SET isdelete=#{isdelete} WHERE id=#{id}")
Boolean updateitempackisdeleteByid( @Param("id") int id, @Param("isdelete") int isdelete);

@Select("SELECT * " +
        "FROM `order`" +
        "WHERE DATE(ordertime) = CURRENT_DATE and status LIKE CONCAT('%', '订单已完成', '%')")
List<Order> gettodayOrderList();

@Select("SELECT * " +
        "FROM `order`" +
        "WHERE DATE(ordertime) = CURRENT_DATE - INTERVAL 1 DAY and status LIKE CONCAT('%', '订单已完成', '%')")
List<Order> getyesterdayOrderList();
    @Select("SELECT * FROM menuitem  ORDER BY Sales DESC LIMIT 10")
    List<MenuItem> getTopTenMenuItemsBySales();

    @Select("SELECT * FROM `itemcomment` WHERE pid=#{pid} ")
 List<ItemComment> getmycommentreplyByuseridandpid(int pid);



}






